SSIS: Append Header/Footer to flat file
Categories: Database; Tagged with: CMD • DTSX • ETL • SSIS • VB; @ November 25th, 2012 17:52Requirement:
Append additional header/footer to the destination flat file:
Solution:
Header/Footer can be added by several ways in SSIS, here is 3 ways I’m using:
Usinig Union in SQL :
-- Header
Select 'START' + convert(varchar(8),GETDATE(), 112)
-- Body
Union All
Select FULLNAME
from OVERSEA_ADDRESS
WHERE POSTAL_CODE = ''
-- Footer
Union All
Select 'END'+ Convert(varchar(8), Count(*))
from TWO_FA_OVERSEA_ADDRESS
WHERE POSTAL_CODE = ''
But sometimes the requirement is more complex, then we need to use Script task.
Script task using VB/C#
We can read/write file using VB or C#, and we can get the variables/paremeters, so basically, we can handle most of the requirement by Script task.
Reading file using VB:
Dim objReader As New System.IO.StreamReader(FILE_NAME)
' Read each line, and append the text into textLines
Dim currentLine As String
Do While objReader.Peek() <> –1
currentLine = objReader.ReadLine()
textLines.Add(currentLine)
Loop
Writing to file
' For loop the textLines
Dim index As Integer
For index = 0 To textLines.Count – 1
Dim text As String = textLines.Item(index).ToString
outStreamWriter.Write(text & vbNewLine)
Next outStreamWriter.Close()
Using copy CMD
Generate the header, body, foot file and use CMD to combine the files:
copy header.txt+body.txt+footer.txt output.txt /y
SSIS: Get Variables/Properties In Script task <->